package com.mes.service;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang.StringUtils;
import org.apache.commons.lang.math.NumberUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.fasterxml.jackson.core.type.TypeReference;
import com.mes.entity.User;
import com.mes.utils.JsonUtil;
import com.yy.base.common.dao.Dao;

@Service
public class QualityCheckService {
	@Autowired
	private Dao dao;

	//初始化质检结果的信息
	public List<Map<String, Object>> initResultInfo() {
		return dao.query("select ad.value, ad.name from ad_ref_list ad where ad.ad_reference_id = '0B80A589DFD64147AD0126954CE05B8A' AND AD.ISACTIVE='Y'");
	}

	//初始化不良原因的信息
	public List<Map<String, Object>> initPoorReasonInfo() {
		return dao.query("select pr.Emesq_Quality_Poor_Reason_id as code,pr.name from Emesq_Quality_Poor_Reason pr where 1=1 ");
	}

	//初始化工序信息
	public List<Map<String, Object>> initProcessInfo() {
		return dao.query("SELECT EP.Emes_PROCESS_ID,EP.NAME,EPR.AD_ORG_ID FROM Emes_PROCESS EP LEFT JOIN Emes_PROCESS_ROUTE EPR ON EP.Emes_PROCESS_ROUTE_ID = EPR.Emes_PROCESS_ROUTE_ID WHERE 1=1 ORDER BY EP.ROUTE_NO ASC");
	}

	//加当前人的默认工序
	public Map<String, Object> getDefaultProcessInfo(String id) {
		return dao.queryUnique(" SELECT AU.DEFAULT_PROCESS_ID FROM AD_USER AU WHERE AU.AD_USER_ID = ? ", id);
	}
    
	//加载花篮对应的订单信息
	public Map<String,Object> getManuOrderInfo(String basketFlowerId) {
		String sql =  "select p.name productName, p.value productCode, to_char(p.weight, '0.000') weight, o.code orderCode, o.qty, o.description,b.emeso_manu_batch_id manuBatchId, " +
				"	to_char(o.planed_date_from, 'YYYY-MM-DD') dateForm, to_char(o.planed_date_to, 'YYYY-MM-DD') dateTo,b.emes_process_id processId,ep.name processName " +
				"  from emeso_manuorder o, m_product p, emeso_manu_in_material im, emeso_manu_batch b,Emeso_Batch_Join bj,emes_process ep,Eam_Facilities ef " +
				" where rownum = 1" +
				"   and p.m_product_id = im.m_product_id" +
				"   and im.emeso_manuorder_id = o.emeso_manuorder_id" +
				"   and im.emeso_manu_in_material_id = b.emeso_manu_in_material_id" +
				"   and ep.emes_process_id = b.emes_process_id " +
				"   and bj.out_conveyance_id = ef.Eam_Facilities_id " +
				"	and ef.code = ? " +
				" order by out_time desc";
		return dao.queryUnique(sql, basketFlowerId);
	}

	public boolean saveQualityCheckResult(String emesoManuBatchId,String result,String r_emesProcessId,String reason,String description,User user,String qty) {
		boolean isOk = false;
		Map<String,Object> guidMap = dao.queryUnique("SELECT SYS_GUID() GUID FROM DUAL");
		String sql = "SELECT EMB.Emeso_MANU_BATCH_ID,EMI.M_PRODUCT_ID FROM Emeso_MANU_BATCH EMB "
					+"LEFT JOIN Emeso_Manu_In_Material EMI ON EMI.Emeso_MANU_IN_MATERIAL_ID = EMB.Emeso_MANU_IN_MATERIAL_ID "
					+"WHERE EMB.Emeso_MANU_BATCH_ID = ?";
		Map<String,Object> manuBatchMap = dao.queryUnique(sql, emesoManuBatchId);
		if(null != guidMap && null != manuBatchMap){
			//保存质检结果信息
			sql = "INSERT INTO Emesq_QUALITY_RESULT(Emeso_MANU_BATCH_ID,Emesq_QUALITY_RESULT_ID,CODE,M_PRODUCT_ID,RESULT,AD_CLIENT_ID,AD_ORG_ID,ISACTIVE,CREATED,CREATEDBY,UPDATED,UPDATEDBY,DESCRIPTION,REASON,QTY)"
					+"VALUES(?,?,GENERATECODE('Emesq_QUALITY_RESULT','CODE',''),?,?,?,?,'Y',now(),?,now(),?,?,?,?)";
			isOk = dao.executeSql(sql, emesoManuBatchId,guidMap.get("GUID"),manuBatchMap.get("M_PRODUCT_ID"),result,user.getClientId(),user.getOrgId(),user.getName(),user.getName(),description,reason,qty) > 0;
			Map<String,Object> r_processMap = dao.queryUnique("SELECT EP.Emes_PROCESS_ID FROM Emes_PROCESS EP WHERE EP.Emes_PROCESS_ID=?", r_emesProcessId);
			if("REWORK".equals(result) && null != r_processMap){
				//是返工，还要调用返工存储过程
				isOk = dao.executeSql("{call MES_QUALITYCHECK.REWORK(?,?)}", emesoManuBatchId,r_emesProcessId) > 0;
			}else if("UNQUALIFIED".equals(result)){
				/*不合格更新批次信息表的状态为 CLOSED*/
				isOk = dao.executeSql("UPDATE Emeso_MANU_BATCH SET STATUS = 'CLOSED' WHERE Emeso_MANU_BATCH_ID = ?",emesoManuBatchId) > 0;
			}
		}
		return isOk;
	}
	
	//初始化工序信息
	public List<Map<String, Object>> initProcessInfo(String orgId) {
		String sql = "SELECT CASE WHEN EP.ROUTE_NO < 90 THEN 'SHOW' ELSE 'HIDE' END AS EPNO," +
				"EP.Emes_PROCESS_ID,EP.NAME,EPR.AD_ORG_ID " +
				"FROM Emes_PROCESS EP ,Emes_PROCESS_ROUTE EPR " +
				"WHERE EP.Emes_PROCESS_ROUTE_ID = EPR.Emes_PROCESS_ROUTE_ID " +
				"AND EPR.AD_ORG_ID = ? ORDER BY EP.ROUTE_NO ASC";
		return dao.query(sql, orgId);
	}
	
	/**
	 * 
	 * @param code
	 * @param processId
	 * @param poorReasonId
	 * @param O_Process
	 * @param u
	 * @param qty
	 * @param desc
	 * @param py
	 * @return
	 */
	public boolean saveProcessRemakeInfos(String code,String processId,String poorReasonId,String O_Process,User u,String qty,String desc,String py){
		
		String insertSql = "INSERT INTO Emeso_REMAKEORDER ( "
				+ "Emeso_REMAKEORDER_ID ,"
				+ "REMAKEDATE,"
				+ "CODE,"
				+ "Emes_PROCESS_ID ,"
				+ "Emesq_QUALITY_POOR_REASON_ID,"
				+ "O_PROCESS,"
				+ "QTY , "
				+ "DESCRIPTION,"
				+ "VENDOR_SHORT_CODE , "
				+ "AD_ORG_ID , "
				+ "AD_CLIENT_ID , "
				+ "ISACTIVE , "
				+ "CREATED , "
				+ "CREATEDBY , "
				+ "UPDATED , "
				+ "UPDATEDBY)"
                          +"VALUES( SYS_GUID(),now() , ? ,?, ?, ?, ?, ?, ? ,"
                          + "'" + u.getOrgId() + "',"
                          + "'" + u.getClientId() + "',"
                          + "'Y',"
                          + "now(),"
                          + "'" + u.getId() + "',"
                          + "now(),"
                          + "'" + u.getId() + "')";
		
		return dao.executeSql(insertSql, code, processId, poorReasonId, O_Process, qty, desc, py) ==1 ;
	}
	
	//加载已上报返工上料信息
	public List<Map<String,Object>> getProcessRemakeInfos(String processId,String orgId){
		List<String> params = new ArrayList<String>();
		String sql = "select er.code,to_char(ER.REMAKEDATE,'yyyy-MM-dd hh24:mi:ss') REMAKEDATE,er.qty,er.vendor_short_code,ep.name curName,ep2.name orginName,EQPR.NAME REASONNAME,au.name subName from Emeso_REMAKEORDER er"+
					" left join emes_process ep on er.emes_process_id=ep.emes_process_id"+
					" left join emesq_quality_poor_reason eqpr on er.emesq_quality_poor_reason_id=eqpr.emesq_quality_poor_reason_id"+
					" left join ad_user au on er.createdby=au.ad_user_id"+
					" left join emes_process ep2 on er.o_process=ep2.emes_process_id where 1=1";
		if(null != processId && !"".equals(processId)){
			sql += " AND er.emes_process_id = ?";
			params.add(processId);
		}
		if(null != orgId && !"".equals(orgId)){
			sql += " AND er.ad_org_id = ?";
			params.add(orgId);
		}
		sql += " order by ER.REMAKEDATE desc";
		//System.out.println(sql);
		return dao.query(sql,params.toArray());
	}
	
	
	/**
	 * 插入返工记录
	 * @param processId
	 * @param u
	 * @param qty
	 * @param desc
	 * @return
	 */
	public boolean saveProcessRemakeInfos(String remakeOrder,String processId,String poorReasonId,User u,String qty,String desc,String orgId){
		
		String insertSql = "insert into emeso_remake ( emeso_remake_id ,code,Emesq_QUALITY_POOR_REASON_ID, emes_process_id , qty , ad_org_id , ad_client_id , isactive , created , createdby , updated , updatedby , descrption)"
                          +"values( sys_guid(),?,?, ?, ?,"
                          + "'" + orgId + "',"
                          + "'" + u.getClientId() + "',"
                          + "'Y',"
                          + "now(),"
                          + "'" + u.getId() + "',"
                          + "now(),"
                          + "'" + u.getId() + "',"
                          +"?)";
		
		return dao.executeSql(insertSql,remakeOrder,poorReasonId,processId,qty,desc) ==1 ;
	}
	

	//通过工序加载返工片的信息列表
	public List<Map<String,Object>> getRemakeInfoByProcess(String remakeId, String processId,String orgId,String receiveDate, String statusQue, String statusSortType){
		List<String> params = new ArrayList<String>();
		String sql = "SELECT ER.Emeso_REMAKE_ID REMAKEID,ER.CODE,EP.NAME PROCESSNAME,ER.QTY," +
				"to_char(ER.CREATED-1/3,'yyyy-MM-dd')||(case when to_number(to_char(ER.CREATED,'hh24')) >=8 and to_number(to_char(ER.CREATED,'hh24')) <20 then ' 白班' else ' 晚班' end) CREATED,ER.CONFIRMQTY," +
				" CASE WHEN ER.ISRECEIVED = 'Y' then to_char(ER.UPDATED, 'yyyy-MM-dd hh24:mi') else null end as receDate, " +
				"(SELECT NAME FROM AD_USER WHERE AD_USER_ID = ER.CREATEDBY) CREATER," +
				"  (SELECT NAME FROM AD_USER WHERE AD_USER_ID = ER.AD_USER_ID_RECEIVE) RECEIVER," +
				" ER.DESCRPTION,ER.ISRECEIVED, ER.ISINSPECTION, " +
				" CASE WHEN ER.ISRECEIVED = 'Y' and ER.ISINSPECTION='Y' THEN '已接收' " +
				"      When ER.ISINSPECTION='Y' THEN '巡检确认' " +
				"      When ER.ISRECEIVED='Y' THEN '生产确认' " +
				"   ELSE '未接收' END AS STATUS," +
				" (er.qty - er.CONFIRMQTY) diffNum, er.DIFF_REASON," +
				" eqpr.name POORREASONNAME FROM Emeso_REMAKE  ER " +
				"  INNER JOIN Emes_PROCESS EP ON ER.Emes_PROCESS_ID = EP.Emes_PROCESS_ID " +
				" left join Emesq_QUALITY_POOR_REASON eqpr on er.Emesq_QUALITY_POOR_REASON_ID=eqpr.Emesq_Quality_Poor_Reason_Id"+
				" left join emes_process_route epr on epr.emes_process_route_id=ep.emes_process_route_id"+
				"  WHERE 1=1 ";
		if(!StringUtils.isBlank(remakeId)) {
			sql += " and er.Emeso_REMAKE_ID = ?";
			params.add(remakeId);
		}
		if(null != processId && !"".equals(processId)){
			sql += " AND EP.Emes_PROCESS_ID = ?";
			params.add(processId);
		}
		if(null != orgId && !"".equals(orgId)){
			sql += " and epr.ad_org_id= ?";
			params.add(orgId);
		}
		if(StringUtils.isNotBlank(statusQue)) {
			if("N".equals(statusQue)) {	//未接收
				sql += " and er.ISRECEIVED is null and er.ISINSPECTION is null ";
			}
			if("Y".equals(statusQue)) {	//已接收
				sql += " and er.ISRECEIVED = 'Y' and er.ISINSPECTION = 'Y'";
			}
			if("inspection".equals(statusQue)) {	//巡检确认
				sql += " and er.ISRECEIVED is null and er.ISINSPECTION = 'Y' ";
			}
			if("production".equals(statusQue)) {	//巡检确认
				sql += " and er.ISRECEIVED = 'Y' and er.ISINSPECTION is null ";
			}
		}
		if(receiveDate != null && receiveDate.indexOf(" ~ ") > -1 ){
			String manuDateFrom = receiveDate.split(" ~ ")[0];
			String manuDateTo = receiveDate.split(" ~ ")[1];
			params.add(manuDateFrom);
			params.add(manuDateTo);
			sql += " AND TO_CHAR(ER.CREATED,'yyyy-MM-dd') >= ? AND TO_CHAR(ER.CREATED,'yyyy-MM-dd') <= ? ";
		}
		if(StringUtils.isNotEmpty(statusSortType)){
			if(statusSortType.equalsIgnoreCase("asc")){
				sql += " order by decode(ER.ISRECEIVED,'Y',0,1),ER.Created desc";
			}else{
				sql += " order by decode(ER.ISRECEIVED,'Y',1,0),ER.Created desc";
			}
		}else{
			sql += " order by ER.Created desc";
		}
		return dao.query(sql,params.toArray());
	}
	
	/**
	 * 获取未确认总数
	 * @param processId
	 * @param orgId
	 * @param receiveDate
	 * @return
	 */
	public double getRemakeInfoCountByProcess(String processId,String orgId,String receiveDate){
		List<String> params = new ArrayList<String>();
		String sql = "SELECT nvl(SUM(ER.QTY),0) SUMQTY FROM Emeso_REMAKE  ER " +
				"  INNER JOIN Emes_PROCESS EP ON ER.Emes_PROCESS_ID = EP.Emes_PROCESS_ID " +
				" left join Emesq_QUALITY_POOR_REASON eqpr on er.Emesq_QUALITY_POOR_REASON_ID=eqpr.Emesq_Quality_Poor_Reason_Id"+
				" left join emes_process_route epr on epr.emes_process_route_id=ep.emes_process_route_id"+
				"  WHERE 1=1 AND (ER.ISRECEIVED IS NULL or er.ISINSPECTION is null) ";
		if(null != processId && !"".equals(processId)){
			sql += " AND EP.Emes_PROCESS_ID = ?";
			params.add(processId);
		}
		if(null != orgId && !"".equals(orgId)){
			sql += " and epr.ad_org_id= ?";
			params.add(orgId);
		}
		if(receiveDate != null && receiveDate.indexOf(" ~ ") > -1 ){
			String manuDateFrom = receiveDate.split(" ~ ")[0];
			String manuDateTo = receiveDate.split(" ~ ")[1];
			params.add(manuDateFrom);
			params.add(manuDateTo);
			sql += " AND TO_CHAR(ER.CREATED,'yyyy-MM-dd') >= ? AND TO_CHAR(ER.CREATED,'yyyy-MM-dd') <= ? ";
		}
		Map<String, Object> map = dao.queryUnique(sql, params.toArray());
		
		if (map != null) {
			return Double.parseDouble(map.get("SUMQTY") + "");
		}
		return 0;
	}

	//确认或拒收返工片的方法
    public Map<String,Object> receiveRemake(String remakeId,Integer confirmReceiveNum, Boolean isReceive,String description,
    		String diffReason, String receiveType, User user) {
		String sql = "";
		if(confirmReceiveNum==null)confirmReceiveNum=0;//默认值
		List<String> params = new ArrayList<String>();
		if("inspection".equals(receiveType)) {
			//巡检确认
			sql = "UPDATE Emeso_REMAKE SET ISINSPECTION='Y', AD_USER_ID_INSPECTION=?, DATE_INSPECTION=now() " +
					"WHERE Emeso_REMAKE_ID = ?";
			params.add(user.getId());
			params.add(remakeId);
		} else {
			if(isReceive){
				sql = "UPDATE Emeso_REMAKE SET ISRECEIVED='Y',AD_USER_ID_RECEIVE=?,UPDATED=now(),UPDATEDBY=?,CONFIRMQTY=?,DIFF_REASON=? " +
						"WHERE Emeso_REMAKE_ID = ?";
				params.add(user.getId());
				params.add(user.getId());
				params.add(confirmReceiveNum.toString());
				params.add(diffReason);
				params.add(remakeId);
			}else{
				sql = "UPDATE Emeso_REMAKE SET ISRECEIVED='N',AD_USER_ID_RECEIVE=?,UPDATED=now(),UPDATEDBY=?,DESCRPTION=? " +
						"WHERE Emeso_REMAKE_ID = ?";
				params.add(user.getId());
				params.add(user.getId());
				params.add(description);
				params.add(remakeId);
			}
		}
		dao.executeSql(sql,params.toArray());
		Map<String,Object> result = new HashMap<String,Object>();
		List<Map<String, Object>> resultList = getRemakeInfoByProcess(remakeId, null, null, null, null, null);
		if(resultList != null && resultList.size() > 0) {
			result = resultList.get(0);
		}
		return result;
    }
    
    /**
     * 查询返工可上料数量
     * @return
     */
    public Map<String,Object> getCanUpQty(String orgId){
    	String sql = "select (select nvl(sum(er.qty),0) from Emeso_REMAKEORDER er where er.ad_org_id=?) upQtySum," +
    			"(select nvl(sum(er.confirmqty),0) confirmqty from Emeso_REMAKE er "+
					" where 1=1 "+
    				" and er.isreceived='Y' "+
					" and er.ad_org_id=?"+
    				" and er.confirmqty is not null) confirmqtySum from dual";
    	return dao.queryUnique(sql,orgId,orgId);
    }

	public List<Map<String, Object>> batchReceive(User user, String data, String receiveType) {
		List<Map<String, Object>> resList = new ArrayList<Map<String, Object>>();
		List<Map<String, Object>> list = JsonUtil.parseJson(data, new TypeReference<List<Map<String, Object>>>(){});
		for (Map<String, Object> map : list) {
			String remakeId = map.get("REMAKEID") + "";
			String isReceived = map.get("ISRECEIVED") + "";
			String isInspection = map.get("ISINSPECTION") + "";
			
			if("inspection".equals(receiveType)) {
				if(!"Y".equals(isInspection)) {
					Map<String, Object> receiveRemake = receiveRemake(remakeId, 0, false, "", "", receiveType, user);
					resList.add(receiveRemake);
				} else {
					map.remove("LAY_CHECKED");
					resList.add(map);
				}
			} else {
				if(!"Y".equals(isReceived)) {
					int confirmReceiveNum = NumberUtils.toInt(map.get("QTY") + "");
					Map<String, Object> receiveRemake = receiveRemake(remakeId, confirmReceiveNum, true, "", "", receiveType, user);
					resList.add(receiveRemake);
				} else {
					map.remove("LAY_CHECKED");
					resList.add(map);
				}
			}
		}
		return resList;
	}
}
